package com.atguigu.gmall.publisher.mapper;

import com.atguigu.gmall.publisher.beans.VisitorStats;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * 访客统计Mapper
 *
 * @author lvbingbing
 * @date 2022-07-07 11:13
 */
public interface VisitorStatsMapper {
    @Select(" select " +
            " is_new, " +
            " sum(uv_ct) as uv_ct, " +
            " sum(pv_ct) as pv_ct, " +
            " sum(sv_ct) as sv_ct, " +
            " sum(uj_ct) as uj_ct, " +
            " sum(dur_sum) as dur_sum " +
            " from visitor_stats_2021 " +
            " where toYYYYMMDD(stt) = #{date} " +
            " group by is_new ")
    List<VisitorStats> selectVisitorStats(Integer date);

    @Select(" select " +
            " toHour(stt) as hr, " +
            " sum(uv_ct) as uv_ct, " +
            " sum(pv_ct) as pv_ct, " +
            " sum(if(is_new = '1', visitor_stats_2021.uv_ct, 0)) new_uv " +
            " from visitor_stats_2021 " +
            " where toYYYYMMDD(stt)=#{date} " +
            " group by hr ")
    List<VisitorStats> selectVisitorStatsByHr(Integer date);
}
